package com.smart.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.smart.model.UnverifiedUser;
import com.smart.model.User;
import com.smart.util.DBUtil;

public class UserDAO {

	public User userLogin(String phone, String password) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "select * from user where telphone_num=? and password=?";
		PreparedStatement pSt = conn.prepareCall(sql);
		pSt.setString(1, phone);
		pSt.setString(2, password);
		ResultSet rs = pSt.executeQuery();
		User user = null;
		if (rs.next()) {
			user = new User();
			user.setUserID(rs.getInt("id"));
			user.setOpenID(rs.getString("open_id"));
			user.setPhone(rs.getString("telphone_num"));
			user.setPassword(rs.getString("password"));
			user.setUserName(rs.getString("nickname"));
			user.setVerified(rs.getInt("verified"));
			user.setHouseID(rs.getInt("house_id"));
		}
		return user;
	}
	public boolean userRegister(String phone, String password) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "insert into user(telphone_num, password, verified) values(?, ?, 0)";
		PreparedStatement pSt = conn.prepareCall(sql);
		pSt.setString(1, phone);
		pSt.setString(2, password);
		int result = pSt.executeUpdate() ;
		return result == 1 ? true : false;
	}
	
	public void addUser(User u,int communityId) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "insert into user(open_id,telphone_num,password,nickname,verified,hourse_id,community_id) values(?,?,?,?,?,?,?)";
		PreparedStatement pSt = conn.prepareCall(sql);
		pSt.setString(1, u.getOpenID());
		pSt.setString(2, u.getPhone());
		pSt.setString(3, u.getPassword());
		pSt.setString(4, u.getUserName());
		pSt.setInt(5, u.getVerified());
		pSt.setInt(6, u.getHouseID());
		pSt.setInt(7, communityId);
		pSt.execute();
	}

	public void delUser(String open_id) throws SQLException {
		System.out.println("add5");
		Connection conn = DBUtil.getConnection();
		String sql = "delete from  user where open_id=?";
		PreparedStatement pSt = conn.prepareCall(sql);
		System.out.println("add6");
		pSt.setString(1, open_id);
		System.out.println("add7");
		pSt.execute();
		System.out.println("add8");
	}

	public boolean updateUserByOpenId(User u) {
		Connection conn = DBUtil.getConnection();
		String sql = "update user set telphone_num=?,nickname=?,verified=?,house_id=? where open_id=?";
		try {
			PreparedStatement pSt = conn.prepareCall(sql);
			pSt.setString(1, u.getPhone());
			pSt.setString(2, u.getUserName());
			pSt.setInt(3, u.getVerified());
			pSt.setInt(4, u.getHouseID());
			pSt.setString(5, u.getOpenID());
			pSt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	public boolean updateUserByVerifyStatus(int userID, int verified) {
		Connection conn = DBUtil.getConnection();
		String sql = "update user set verified=? where id=?";
		try {
			PreparedStatement pSt = conn.prepareCall(sql);
			pSt.setInt(1, verified);
			pSt.setInt(2, userID);
			pSt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	public List<UnverifiedUser> getUnverifiedUser() throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "select user_id, name, telphone_num, community_name, building_no, room_no from unverified_user";
		PreparedStatement pSt = conn.prepareCall(sql);
		ResultSet rs = pSt.executeQuery();
		ArrayList<UnverifiedUser> userList = new ArrayList<UnverifiedUser>();
		while (rs.next()) {
			UnverifiedUser uu = new UnverifiedUser();
			uu.setUserID(rs.getInt(1));
			uu.setName(rs.getString(2));
			uu.setTelephone_num(rs.getString(3));
			uu.setCommunity_name(rs.getString(4));
			uu.setBuilding_no(rs.getInt(5));
			uu.setRoom_no(rs.getInt(6));
			userList.add(uu);
		}

		return userList;
	}

	public List<User> selectUser(int communityId) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "select * from  user where community_id=?";
		PreparedStatement pSt = conn.prepareCall(sql);
		pSt.setInt(1, communityId);
		ResultSet res = pSt.executeQuery();
		List<User> list = new ArrayList<User>();
		while (res.next()) {
			User u = new User();
			u.setUserID(res.getInt("id"));
			u.setOpenID(res.getString("open_id"));
			u.setPhone(res.getString("telphone_num"));
			u.setPassword(res.getString("password"));
			u.setUserName(res.getString("nickname"));
			u.setVerified(res.getInt("verified"));
			u.setHouseID(res.getInt("house_id"));
			list.add(u);
		}
		System.out.println("select");
		return list;
	}

	public int getIdByTelephoneNum(String telephoneNum,int communityId) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "select id from  user where telphone_num=? and community_id=?";
		PreparedStatement pSt = conn.prepareCall(sql);
		pSt.setString(1, telephoneNum);
		pSt.setInt(2, communityId);
		ResultSet res = pSt.executeQuery();
		if (res.next()) {
			return res.getInt(1);
		}
		return -1;
	}

	public String getOpenIdByTel(String telephoneNum) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "select open_id from  user where telphone_num=?";
		PreparedStatement pSt = conn.prepareCall(sql);
		pSt.setString(1, telephoneNum);
		ResultSet res = pSt.executeQuery();
		if (res.next()) {
			String openId = res.getString("open_id");
			System.out.println(openId);
			return openId;
		}
		return null;

	}

	public String getOpenIdByUserId(int id) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "select open_id from  user where id=?";
		PreparedStatement pSt = conn.prepareCall(sql);
		pSt.setInt(1, id);
		ResultSet res = pSt.executeQuery();
		if (res.next()) {
			String openId = res.getString("open_id");
			System.out.println(openId);
			return openId;
		}
		return null;
	}

	public boolean regOper(String openId, String nickName, String telephoneNum,int communityId) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "update user set open_id=? , nickname=? , verified=? where telphone_num=? and community_id=?";
		PreparedStatement pSt = conn.prepareCall(sql);
		pSt.setString(1, openId);
		pSt.setString(2, nickName);
		pSt.setInt(3, 1);
		pSt.setString(4, telephoneNum);
		pSt.setInt(5,communityId);
		int x = pSt.executeUpdate();
		return x > 0;
	}

	// pageIndex为第几页，pageSize为每页多少条记录
	public List<User> getUserList(String name, String telephoneNum, String buildingNo, String roomNo, int pageIndex,int communityId)
			throws SQLException {
		int pageSize = 10;// 默认一页显示10条记录
		List<User> userList = new ArrayList<User>();
		Connection conn = DBUtil.getConnection();
		Statement stmt = conn.createStatement();
		String sql = "select  user.id,name,telphone_num,house.id,building_no,room_no,type from user,house where house_id=house.id and user.community_id="+communityId;
		if (name != null && !name.trim().equals("")) {
			sql += " and name=" + "'" + name + "'";
		}
		if (telephoneNum != null && !telephoneNum.trim().equals("")) {
			sql += " and telphone_num=" + "'" + telephoneNum + "'";
		}
		if (buildingNo != null && !buildingNo.trim().equals("")) {
			sql += " and building_no=" + buildingNo;
		}
		if (roomNo != null && !roomNo.trim().equals("")) {
			sql += " and room_no=" + roomNo;
		}
		sql += " limit " + (pageIndex - 1) * pageSize + "," + pageSize;
		System.out.println("sql:" + sql);
		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next()) {
			User user = new User();
			user.setUserID(rs.getInt("id"));
			user.setUserName(rs.getString("nickname"));
			user.setPhone(rs.getString("telphone_num"));
			user.setHouseID(rs.getInt("house_id"));
			
			userList.add(user);
		}
		return userList;
	}

	// 获取总数
	public int getUserCount(String name, String telephoneNum, String buildingNo, String roomNo,int communityId) throws SQLException {
		System.out.println("UserDAo getUserCount method..");
		int count = -1;
		String sql = "select  count(*) from user,house where house_id=house.id and user.community_id="+communityId;
		if (name != null && !name.trim().equals("")) {
			sql += " and name=" + "'" + name + "'";
		}
		if (telephoneNum != null && !telephoneNum.trim().equals("")) {
			sql += " and telphone_num=" + "'" + telephoneNum + "'";
		}
		if (buildingNo != null && !buildingNo.trim().equals("")) {
			sql += " and building_no=" + buildingNo;
		}
		if (roomNo != null && !roomNo.trim().equals("")) {
			sql += " and room_no=" + roomNo;
		}
		System.out.println(sql);
		Connection conn = DBUtil.getConnection();
		PreparedStatement pSt = conn.prepareStatement(sql);
		ResultSet res = pSt.executeQuery();
		if (res.next())
			count = res.getInt(1);
		return count;
	}

	// 添加用户
	public boolean addUser(String name, String telephoneNum, int houseId, int type,int communityId) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "insert into user(name,telphone_num,house_id,type,community_id) values(?,?,?,?,?)";
		PreparedStatement pSt = conn.prepareCall(sql);
		pSt.setString(1, name);
		pSt.setString(2, telephoneNum);
		pSt.setInt(3, houseId);
		pSt.setInt(4, type);
		pSt.setInt(5, communityId);
		int x = pSt.executeUpdate();
		return x > 0;
	}

	// 通过id删除用户
	public boolean deleteUserById(int id) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "delete from  user where id=?";
		PreparedStatement pSt = conn.prepareCall(sql);
		pSt.setInt(1, id);
		int x = pSt.executeUpdate();
		return x > 0;
	}

	public boolean updateUser(int id, String telephoneNum, String name, int type, int houseId) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "update user set telphone_num=?,name=?,type=?,house_id=? where id=?";
		PreparedStatement pSt = conn.prepareCall(sql);
		pSt.setString(1, telephoneNum);
		pSt.setString(2, name);
		pSt.setInt(3, type);
		pSt.setInt(4, houseId);
		pSt.setInt(5, id);
		int x = pSt.executeUpdate();
		System.out.println("x:"+x);
		return x > 0;
	}
	
	public int getVerifiedByOpenId(String openId) throws SQLException
	{
		int verified=0;
		Connection conn = DBUtil.getConnection();
		String sql = "select verified from  user where open_id=?";
		PreparedStatement pSt = conn.prepareCall(sql);
		pSt.setString(1, openId);
		ResultSet res = pSt.executeQuery();
		if (res.next()) {
			verified = res.getInt(1);
		}
		return verified;
	}
}
